from itemadapter import ItemAdapter
import pymysql
from twisted.enterprise import adbapi
import hashlib
from pymysql.cursors import DictCursor

class MysqlPipeline(object):
    def __init__(self, dbpool):
        self.dbpool = dbpool

    @classmethod
    def from_crawler(cls, crawler):
        args = dict(host=crawler.settings.get("MYSQL_HOST"),
                    port=crawler.settings.get("MYSQL_PORT"),
                    user=crawler.settings.get("MYSQL_USER"),
                    db=crawler.settings.get("MYSQL_DBNAME"),
                    passwd=crawler.settings.get("MYSQL_PASSWORD"),
                    charset=crawler.settings.get("MYSQL_CHARSET"),
                    cursorclass=DictCursor)

        dbpool = adbapi.ConnectionPool("pymysql", **args)
        return cls(dbpool)

    def insert_sql(self, cursor, item):
        data = dict(item)
        keys = ','.join(data.keys())
        values = ','.join([item[list(data.keys())[i]] for i in range(len(list(data.keys())))])
        tablename = "stocks"

        try:
            item['name'] = item['name']
            tablename = 'senti'
        except:
            pass

        sql = 'insert into %s (%s) values (%s)' % (tablename, keys, values)
        print(sql)
        cursor.execute(sql)

    def process_item(self, item, spider):
        result = self.dbpool.runInteraction(self.insert_sql, item)

"""class stockPipeline:
    def __init__(self):
        self.conn = pymysql.connect(host="127.0.0.1", user="root", password="", database="demos",
                                    charset="utf8")
        self.db_cur = self.conn.cursor()

    def close_spider(self, spider):
        self.conn.close()

    def process_item(self, item, spider):
        if spider.name == "stock":
            val = (item['tscode'],
                   item['tradedate'],
                   item['open'],
                   item['close'],
                   item['high'],
                   item['low'],
                   item['vol'],
                   item['amount']
                   )
            sql = "insert into stocks(tscode, trateDate, open, close, high, low, vol, amount) VALUES (%s, %s, %s, %s, %s, %s, %s, %s)"
            self.db_cur.execute(sql, val)
            self.db_cur.commit()
        return item

class sentiPipeline:
    def __init__(self):
        self.conn = pymysql.connect(host="127.0.0.1", user="root", password="", database="demos",
                                    charset="utf8")
        self.db_cur = self.conn.cursor()

    def close_spider(self, spider):
        self.conn.close()

    def process_item(self, item, spider):
        if spider.name == "senti":
            val = (item['name'], item['str'])
            sql = "insert into senti(name, str) VALUES(%s, %s)"
            self.db_cur.execute(sql, val)
            self.db_cur.commit()
        return item
class MysqlPipeline(object):
    def __init__(self, host, database, user, password, port):
        self.host = host
        self.database = database
        self.user = user
        self.password = password
        self.port = port

    @classmethod
    def from_crawler(cls, crawler):
        return cls(
            host=crawler.settings.get('MYSQL_HOST'),
            database = crawler.settings.get('MYSQL_DATABASE'),
            user = crawler.settings.get('MYSQL_USER'),
            password = crawler.settings.get('MYSQL_PASSWORD'),
            port = crawler.settings.get('MYSQL_PORT')
        )

    def open_spider(self, spider):
        self.db = pymysql.connect(self.host, self.user, self.password, self.database, charset='utf-8',
                                  port=self.port)
        self.cursor = self.db.cursor()

    def process_item(self, item, spider):

        self.db.commit()
        return item

# MySQL交互:"""
